﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetDistribution_CreateByXml')
	DROP PROCEDURE up_UtilityAssetDistribution_CreateByXml
GO


CREATE PROCEDURE [dbo].[up_UtilityAssetDistribution_CreateByXml]
(
	@vendorProjectId int,
	@xml			xml
) 
AS

DECLARE @UtilityId int
DECLARE @UtilityProjectId int

--Gets the Utility ID
SELECT 
	@UtilityId = UtilityFk
FROM 
	tb_vendor_Projects
WHERE  
	Vendor_ProjectId = @vendorProjectId

--Gets the Utility Project Id
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId

PRINT 'Prepare table used to update/insert assets'

declare @assetsToCreate as table (DistributionInspectionId int, DistributionAssetId int)

INSERT INTO @assetsToCreate
(
	DistributionInspectionId,
	DistributionAssetId
)
SELECT 
	Item.query('.').value('(VisitToAsset/Visit)[1]', 'int') as VisitId,
	CASE 
		WHEN Item.query('.').value('(VisitToAsset/Asset)[1]', 'int') = 0 THEN NULL
		ELSE Item.query('.').value('(VisitToAsset/Asset)[1]', 'int')
	END as AssetId
FROM
	@xml.nodes('/VisitsToAssets/VisitToAsset') T(Item) 

PRINT 'Update Asset Fk from Distribution inspection'

UPDATE 
	tb_Vendor_Distribution_Poles
SET
	Utility_Asset_Distributions_PoleFk = DistributionAssetId
FROM 
	@assetsToCreate
WHERE
	Vendor_Distribution_Poles_PoleID = DistributionInspectionId

PRINT 'Add Assets from visits'

DECLARE @insertedAssetIds TABLE (Utility_Asset_DistributionId int, DistributionPoleId int)

INSERT INTO tb_Utility_Asset_Distributions
(
	UtilityFK,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	Handheld,
	Distribution_TopTypeFk,
	Utility_ContactFK,
	Date,
	Utility_ProjectFK,
	MapFk,
	SectionFk,
	[Year],
	TreatmentFk,
	ClassFk,
	SpeciesFk,
	Length,
	Number,
	CreatedOn,
	CreatedBy,
	UtilityKey,
	UtilityGroup,
	D804
)
OUTPUT INSERTED.Utility_Asset_DistributionId, INSERTED.Altitude INTO @insertedAssetIds
SELECT 
	@UtilityId,
	poles.Latitude,
	poles.Longitude,
	poles.Vendor_Distribution_Poles_PoleID, -- TODO: Change this, Using Altitude as temp id
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	GETDATE(),
	@UtilityProjectId,
	poles.MapFk,
	poles.SectionFk,
	poles.[Year],
	poles.TreatmentFk,
	poles.ClassFK,
	poles.SpecieFk,
	poles.LengthFk,
	poles.Number,
	NULL,
	'up_DistributionInspVisitToDistributionAsset',
	NULL,
	NULL,
	D804
FROM 
	tb_Vendor_Distribution_Poles poles
WHERE
	poles.ProjectFk = @vendorProjectId
	AND poles.Utility_Asset_Distributions_PoleFk IS NULL
	AND poles.Vendor_Distribution_Poles_PoleId in (SELECT DistributionInspectionId FROM @assetsToCreate)

PRINT 'Updates vendor distribution pole foreign key to asset'

UPDATE 
	tb_Vendor_Distribution_Poles 
SET
	Utility_Asset_Distributions_PoleFk = [InsertedIds].Utility_Asset_DistributionId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_Distribution_Poles.Vendor_Distribution_Poles_PoleID = [InsertedIds].DistributionPoleId

UPDATE 
	tb_Utility_Asset_Distributions
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_Distributions
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_Distributions.Utility_Asset_DistributionId = [InsertedIds].Utility_Asset_DistributionId

PRINT 'Add asset phases'
-- If one asset has phases assigned, new records are not inserted

INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT 
	assets.Utility_Asset_DistributionId,
	phases.Vendor_Distribution_PhaseFk
FROM		
	@insertedAssetIds [InsertedIds]
INNER JOIN tb_Utility_Asset_Distributions assets
	ON assets.Utility_Asset_DistributionId = [InsertedIds].Utility_Asset_DistributionId
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON poles.Vendor_Distribution_Poles_PoleID = [InsertedIds].DistributionPoleId
INNER JOIN tb_Vendor_Distribution_PhasesXPole phases
	ON phases.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleID


PRINT 'Add asset tops'
-- If one asset has phases assigned, new records are not inserted

INSERT INTO tb_Utility_Asset_Distribution_TopsXPole
(
	Utility_Asset_DistributionFk,
	TopTypeFk
)
SELECT 
	assets.Utility_Asset_DistributionId,
	tops.Vendor_Distribution_TopFk
FROM		
	@insertedAssetIds [InsertedIds]
INNER JOIN tb_Utility_Asset_Distributions assets
	ON assets.Utility_Asset_DistributionId = [InsertedIds].Utility_Asset_DistributionId
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON poles.Vendor_Distribution_Poles_PoleID = [InsertedIds].DistributionPoleId
INNER JOIN tb_Vendor_Distribution_TopsXPole tops
	ON tops.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleID
